//    Openbravo POS is a point of sales application designed for touch screens.
//    Copyright (S) 2007-2009 Openbravo, S.L.
//    http://www.openbravo.com/product/pos
//
//    This file is part of Openbravo POS.
//
//    Openbravo POS is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    Openbravo POS is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with Openbravo POS.  If not, see <http://www.gnu.org/licenses/>.

package com.openbravo.data.loader.sqlbuilder;

import com.openbravo.data.basic.BasicException;
import com.openbravo.data.loader.serialize.QBFParameterDataWrite;
import com.openbravo.data.loader.serialize.serializer.SerializerWrite;
import e.odbo.data.dsl.query.LinkQBFParameter;
import e.odbo.data.dsl.query.LinkQBFParameters;
import e.odbo.data.dsl.query.QBFCompareEnum;
import e.odbo.data.dsl.query.QBFParameter;
import e.odbo.data.dsl.query.QBFParameters;
import com.openbravo.data.loader.serialize.DataWriteUtils;

import java.util.Date;

/**
 * @author adrian
 */
public class QBFBuilder implements ISQLBuilderStatic {

    private String m_sSentNullFilter;   // la sentencia que se devuelve cuando el filtro es vacio
    private String m_sSentBeginPart;  // La sentencia que se devuelve es m_sSentBeginPart + ( filtro ) + m_sSentEndPart
    private String m_sSentEndPart;

    private String[] m_asFindFields;

    //    /** Creates a new instance of QBFBuilder */
//    public QBFBuilder(TableDefinition tb, String[] asFindFields) {
//        StringBuffer sent = new StringBuffer();
//        sent.append("select ");
//        for (int i = 0; i < tb.getFields().length; i ++) {
//            if (i > 0) {
//                sent.append(", ");
//            }
//            sent.append(tb.getFields()[i]);
//        }
//        sent.append(" from ");
//        sent.append(tb.getTableName());
//        m_sSentNullFilter = sent.toString();
//        sent.append(" where ");
//        m_sSentBeginPart = sent.toString();
//        m_sSentEndPart = "";
//        m_asFindFields = asFindFields;
//    }

    public QBFBuilder(String sSentence, String[] asFindFields) {
        int iPos = sSentence.indexOf("?(QBF_FILTER)");
        if (iPos < 0) {
            m_sSentBeginPart = sSentence;
            m_sSentEndPart = "";
            m_sSentNullFilter = sSentence;
        } else {
            m_sSentBeginPart = sSentence.substring(0, iPos);
            m_sSentEndPart = sSentence.substring(iPos + 13);
            m_sSentNullFilter = m_sSentBeginPart + "(1=1)" + m_sSentEndPart;
        }
        m_asFindFields = asFindFields;
    }

    public QBFBuilder(String sSentence) {
        int iPos = sSentence.indexOf("?(QBF_FILTER)");
        if (iPos < 0) {
            m_sSentBeginPart = sSentence;
            m_sSentEndPart = "";
            m_sSentNullFilter = sSentence;
        } else {
            m_sSentBeginPart = sSentence.substring(0, iPos);
            m_sSentEndPart = sSentence.substring(iPos + 13);
            m_sSentNullFilter = m_sSentBeginPart + "(1=1)" + m_sSentEndPart;
        }
    }

    public String getSQL(SerializerWrite sw, Object params) throws BasicException {
        if (params != null && params instanceof QBFParameters) {
            return getSQL((QBFParameters) params);
        }

        if (params != null && params instanceof QBFParameter) {
            String sFilter1 = getParameterSQL((QBFParameter) params);
            if (sFilter1.length() == 0)
                return m_sSentNullFilter; // no hay filtro
            return m_sSentBeginPart + "(" + sFilter1 + ")" + m_sSentEndPart;
        }

        if (sw == null || params == null) {
            return m_sSentNullFilter;
        } else {
            QBFParameterDataWrite mydw = new QBFParameterDataWrite(m_asFindFields);
            sw.writeValues(mydw, params);
            String sFilter = getFilterSQL(mydw);
            if (sFilter.length() == 0) {
                return m_sSentNullFilter; // no hay filtro
            } else {
                return m_sSentBeginPart + "(" + sFilter + ")" + m_sSentEndPart; // incluimos el filtro
            }
        }
    }

    public String getSQL(QBFParameters params) throws BasicException {
        if (params == null) {
            return m_sSentNullFilter;
        } else {
            String sFilter = getFilterSQL(params);
            if (sFilter.length() == 0) {
                return m_sSentNullFilter; // no hay filtro
            } else {
                return m_sSentBeginPart + "(" + sFilter + ")" + m_sSentEndPart; // incluimos el filtro
            }
        }
    }

    public static String getSQLFilter(String fieldName, Date startTime, Date endTime) {
        QBFParameters paras = new QBFParameters();
        if (startTime != null)
            paras.and(new QBFParameter(fieldName).greaterOrEqual(startTime));
        if (endTime != null)
            paras.and(new QBFParameter(fieldName).lessOrEqual(endTime));
        return getFilterSQL(paras);
    }

    public static String getFilterSQL(QBFParameters paras) {

        checkParameters(paras);

        StringBuilder sFilter = new StringBuilder();
        String sItem;
        boolean flag = false;
        for (int i = 0; i < paras.getAndList().size(); i++) {
            sItem = getParameterSQL(paras.getAndList().get(i));
            if (sItem != null) {
                if (flag) {
                    sFilter.append(" AND ");
                } else
                    flag = true;
                sFilter.append(sItem);
            }
        }
        for (int i = 0; i < paras.getOrList().size(); i++) {
            sItem = getParameterSQL(paras.getOrList().get(i));
            if (sItem != null) {
                if (flag) {
                    sFilter.append(" OR ");
                } else
                    flag = true;
                sFilter.append(sItem);
            }
        }
        for (QBFParameters subparas : paras.getAndSubList()) {
            if (flag) {
                sFilter.append(" AND ");
            }
            sFilter.append("(");
            sFilter.append(getFilterSQL(subparas));
            sFilter.append(")");
        }

        for (QBFParameters subparas : paras.getOrSubList()) {
            if (flag) {
                sFilter.append(" OR ");
            }
            sFilter.append("(");
            sFilter.append(getFilterSQL(subparas));
            sFilter.append(")");
        }
        return sFilter.toString();
    }

    private static void checkParameters(QBFParameters paras) {
        if (paras.getAndList().size() == 0 && paras.getOrList().size() == 0)
            paras.and(QBFParameter.ALL);
    }

    private static String getParameterSQL(QBFParameter parameter) {
        String sqlValue;
        if (QBFCompareEnum.COMP_IN.equals(parameter.getQbf()) && parameter.getValue() instanceof LinkQBFParameter) {
            LinkQBFParameter linkQbfParameter = (LinkQBFParameter) parameter.getValue();
            String fieldName = linkQbfParameter.getAlise() == null ? linkQbfParameter.getFieldName() : linkQbfParameter.getAlise() + "." + linkQbfParameter.getFieldName();
            String tableName = linkQbfParameter.getAlise() == null ? linkQbfParameter.getTableName() : linkQbfParameter.getTableName() + " " + linkQbfParameter.getAlise();
            sqlValue = "SELECT " + fieldName + " FROM " + tableName + "  WHERE " + getParameterSQL(linkQbfParameter.getQbfParameter());
        }
        if (QBFCompareEnum.COMP_IN.equals(parameter.getQbf()) && parameter.getValue() instanceof LinkQBFParameters) {
            LinkQBFParameters linkQbfParameters = (LinkQBFParameters) parameter.getValue();
            String fieldName = linkQbfParameters.getAlise() == null ? linkQbfParameters.getFieldName() : linkQbfParameters.getAlise() + "." + linkQbfParameters.getFieldName();
            String tableName = linkQbfParameters.getAlise() == null ? linkQbfParameters.getTableName() : linkQbfParameters.getTableName() + " " + linkQbfParameters.getAlise();
            sqlValue = "SELECT " + fieldName + " FROM " + tableName + "  WHERE " + getFilterSQL(linkQbfParameters.getQbfParameters());
        } else
            sqlValue = DataWriteUtils.getSQLValue(parameter.getValue());
        return getExpression(parameter.getFieldName(),parameter.getQbf(), sqlValue);
    }

    private static String getExpression(String sField,QBFCompareEnum compareEnum,String sSQLValue){
        String retStr=null;
        switch (compareEnum){
            case COMP_NONE:
                break;
            case COMP_ALL:
                retStr="1=1";
                break;
            case COMP_ISNULL:
                retStr=sField + " IS NULL";
                break;
            case COMP_ISNOTNULL:
                retStr=sField + " IS NOT NULL";
                break;
            case COMP_RE:
                sSQLValue.replace("％", "%");
                if (sSQLValue.startsWith("'"))
                    if (sSQLValue.endsWith("'"))
                        sSQLValue = sSQLValue.substring(1, sSQLValue.length() - 1);
                    else
                        sSQLValue = sSQLValue.substring(1);
                if (!sSQLValue.startsWith("%"))
                    sSQLValue = "%" + sSQLValue;
                if (!sSQLValue.endsWith("%"))
                    sSQLValue += "%";
                retStr= sField + " LIKE '" + sSQLValue + "'";
                break;
            case COMP_EQUALS:
                retStr=sField + " = " + sSQLValue;
                break;
            case COMP_DISTINCT:
                retStr=sField + " <> " + sSQLValue;
                break;
            case COMP_GREATER:
                retStr=sField + " > " + sSQLValue;
                break;
            case COMP_LESS:
                retStr=sField + " < " + sSQLValue;
                break;
            case COMP_GREATEROREQUALS:
                retStr=sField + " >= " + sSQLValue;
                break;
            case COMP_LESSOREQUALS:
                retStr=sField + " <= " + sSQLValue;
                break;
            case COMP_IN:
                retStr=sField + " IN ( " + sSQLValue + " )";
                break;
        }
        return retStr==null?"":retStr;
    }

    private static String getFilterSQL(QBFParameterDataWrite mydw) {
        StringBuilder sFilter = new StringBuilder();
        String sItem;
        String[] m_asFindFields= mydw.getM_asFindFields();
        QBFCompareEnum[] m_aiCondFields=mydw.getM_aiCondFields();
        String[] m_aParams=mydw.getM_aParams();
        for (int i = 0; i < m_aiCondFields.length; i++) {
            sItem = getExpression(m_asFindFields[i],m_aiCondFields[i], m_aParams[i]);
            if (sItem != null) {
                if (sFilter.length() > 0) {
                    sFilter.append(" AND ");
                }
                sFilter.append(sItem);
            }
        }
        return sFilter.toString();
    }

}
